import os
import sys
import xlsxwriter  # 批量写入表
import xlrd  # 打开表
import re
import time
fileList = []


def filePath(pathList):
    for root, dir, files in os.walk(pathList):
        for file in files:
            filename = os.path.join(root, file)
            filename = filename.strip()
            if 'xls' in filename or 'xlsx' in filename:
                fileList.append(filename)


def writeFile(path, data, header):
    with open(path, 'a', encoding='utf-8') as f:
        for line in header:
            for v in line:
                f.write(str(v))
                f.write('\t')
            f.write('\n')
        for line in data:
            for v in line:
                f.write(str(v))
                f.write('\t')
            f.write('\n')


# 导入表，将excel表的数据变成要处理的数据形式。
def readFile(readpath):
    book = xlrd.open_workbook(readpath)  # 打开需要操作的excel表
    for sheetName in book.sheet_names():  # 顺序读取每一个sheet
        sheet = book.sheet_by_name(sheetName)  # 根据名字获取sheet
        resList = []  # 生成文件数据
        for line in sheet.get_rows():
            tempList = []
            for text in line:
                k = text.ctype
                v = text.value
                # tempList.append((k, v))
                tempList.append(v)
            resList.append(tempList)
        printStr = str(readpath + '\t' + sheetName + '\t' + '读取成功！！！')
        print(printStr)
        yield resList[1:]  # 返回每个sheet页数据


if __name__ == '__main__':
    pathList = input('文件夹路径：\n')
    pathListw = pathList+'\\HJ_CX.csv'
    filePath(pathList)
    header = [['qh', 'bxnd']]
    j = 0
    for i in fileList:
        data = readFile(i)
        print(i)
        for sheet in data:
            pathListww = pathListw.split('.')[0] + '_' + str(j) + '.csv'
            j += 1
            writeFile(pathListww, sheet, header)
            print(i + ' --> ' + pathListww + ' 写入成功！！！')

    print('OK!!!')
    sys.exit()